SAMPLE 4

DECLARE @vInputLength INT, @vIndex INT, @pInput VARCHAR(100), @LocCount int, @MySQL varchar(300) --,@vCount  
-- Needed variables for below loops.  @vCount is used for troubleshooting only

--SET @vCount = 0; -- Sets the count of spaces to zero
SET @vIndex = 1; -- Sets the starting position of the string to 1
Select @LocCount = Count(Location) from dbo.#MyTemp1; -- Counts the # of distinct locations in temp table for outer loop

While @LocCount > 0  -- While we still have locations to browse

Begin

Set @pInput = (Select Top 1 Location from dbo.#MyTemp1 where Done = 0); -- Get the first "unfixed" location

SET @vInputLength = LEN(@pInput); --Get the total string length

		WHILE @vIndex <= @vInputLength -- While we still have characters to search
		BEGIN
		 
			IF SUBSTRING(@pInput, @vIndex, 1) = ' '  --If the current character is a space
			 Begin
				--SET @vCount = @vCount + 1;  --Add one to the spaces count. Use only if verifying code.

--				Update mt1
--				Set NumSpaces = @vCount, Pos = ISNULL(mt1.Pos,'') + ' ' + Convert(varchar(2),@vIndex)
--				from dbo.#MyTemp1 mt1 where Location = @pInput;
				--This table is for testing purposes only, to verify space count code is working correctly.

				Set @MySQL = 'Update mt1  Set Location = Substring(mt1.Location,1,' + Convert(varchar(3),@vIndex) + ') '
					+ '+ UPPER(Substring(mt1.Location,' + Convert(varchar(3),(@vIndex+1)) +',1))'
					+ ' + Substring(mt1.Location,' + Convert(varchar(3),(@vIndex+2)) + ',Len(mt1.Location)) from dbo.#MyTemp1 mt1'
					+ ' Where Location = ''' + @pInput + ''' and Done = 0;'; 
					-- Dynamic SQL. Will update the character AFTER the current space to an Upper Case character
				
				--Select @MySQL; --For testing & verification of SQL String only
			   Exec (@MySQL); -- Excecute the update code set above

			 End  -- END of IF statement

			SET @vIndex = @vIndex + 1 --Increment the position counter
		--Select @pInput as Loc, @vIndex as Pos, @vCount as Cnt  --For testing & verification of variables only.

		 END -- End of singular Location string search / Inner WHILE loop

	Set @LocCount = @LocCount - 1;  --Decrement the # of locations to search
	--Set @vCount = 0;  --Reset the spaces counter
	Set @vIndex = 1;  --Reset the position counter

	Update dbo.#MyTemp1
	Set Done = 1
	where Location = @pInput; --Update current location as fixed
	
END -- End of Outer While loop. All Locations fixed

GO


-----------------------------------------------------------------------

SAMPLE 5


Select * from dbo.#MyTemp1 
-- Shows fixed Temp table

Update idf
Set Location = mt1.Location
from dbo.Import_Data_Filter idf
join dbo.#MyTemp1 mt1
on idf.Location = mt1.Location;
--Updates Real Table

Select * from dbo.Import_Data_Filter;
--Shows results of update

-------------------------------------------------------------------------